
create database BugRS
go

create table Admin(
	adminD int identity(1,1) primary key,
	Account varchar(50),
	Password varchar(50),
	[Name] varchar(50),
	Email varchar(100),
	Phone varchar(50)
)
go

create table Department(
		DepartmentID int identity(1,1) primary key,
		DepartmentName varchar(50)
)
go

create table Staff(
	StaffID int identity(1,1) primary key,
	StaffName varchar(100),
	DepartmentID int foreign key references Department(DepartmentID),
	UserName varchar(50),
	PassWord varchar(50)
)
go

create table Customer(
	CusID int identity(1,1) primary key,
	CusName varchar(50),
	CusAddress varchar(200),
	CusEmail varchar(100),
	CusPhone varchar(50),
	CusAccount varchar(50),
	CusPass varchar(50)
)
go


create table Product(
	ProID int identity(1,1) primary key,
	ProName varchar(100),
	ProWarranty int,
	ProDescribe varchar(200),
	ProQuantity int
)
go


create table [Order](
	OrderID int  identity(1,1) primary key,
	Date datetime,
	CusID int foreign key references Customer(CusID)
)
go

create table OrderDetails(
	DispatchNo int primary key,
	OrderID int references [Order](OrderID),
	ProID int references Product(ProID),
	Quantity int
)
go

create table Complaint(
	ComplaintID int identity(1,1) primary key,
	DispatchNo int foreign key references [OrderDetails](DispatchNo),
	ComTitle varchar(100),
	ComContent varchar(2000),
    ComDate datetime,
	CusID int foreign key references Customer(CusID),
	DepartmentID int foreign key references Department(DepartmentID),
	Status_1 bit,
	TransferDate datetime,
	Status_2 bit
)
go

create table Response(
	ComplaintID int foreign key references Complaint(ComplaintID),
	StaffID int foreign key references Staff(StaffID),
	Primary key(ComplaintID,StaffID),
	ResTitle varchar(200),
	ResContent varchar(2000),
	ResDate datetime,
	Status bit,
)
